﻿
-- Create partitions table
CREATE TABLE [dbo].[Partitions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[WebApplicationId] [uniqueidentifier] NOT NULL,
	[SiteId] [uniqueidentifier] NULL,
	[WebId] [uniqueidentifier] NULL,
	[ListId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Partitions] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Create the storage table
CREATE TABLE [dbo].[Storage](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PartitionId] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[TypeFullName] [nvarchar](256) NOT NULL,
	[CreatedBy] [nvarchar](256) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedBy] [nvarchar](256) NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[Data] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_Storage] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Storage]  WITH CHECK ADD  CONSTRAINT [FK_Storage_Partitions] FOREIGN KEY([PartitionId])
REFERENCES [dbo].[Partitions] ([Id])
GO

ALTER TABLE [dbo].[Storage] CHECK CONSTRAINT [FK_Storage_Partitions]
GO

-- =============================================
-- Author:		Eswar Prakash
-- Create date: 01/02
-- Description:	Ensures that a partition exists for the given SP context
-- =============================================
CREATE PROCEDURE [dbo].[sp_EnsurePartition]
	-- Add the parameters for the stored procedure here
	@WebApplicationId uniqueidentifier,
	@SiteId uniqueidentifier,
	@WebId uniqueidentifier,
	@ListId uniqueidentifier,
	@PartitionId int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @ListId IS NOT NULL
    BEGIN
		IF NOT EXISTS(SELECT [Id] FROM [Partitions] WHERE 
			[WebApplicationId] = @WebApplicationId AND [SiteId] = @SiteId AND
			[WebId] = @WebId AND [ListId] = @ListId)
			INSERT INTO
				[Partitions] (
				[WebApplicationId],
				[SiteId],
				[WebId],
				[ListId] )
			VALUES (
				@WebApplicationId,
				@SiteId,
				@WebId,
				@ListId
			)
	END
    ELSE IF @WebId IS NOT NULL
    BEGIN
		IF NOT EXISTS(SELECT [Id] FROM [Partitions] WHERE 
			[WebApplicationId] = @WebApplicationId AND [SiteId] = @SiteId AND
			[WebId] = @WebId)
			INSERT INTO
				[Partitions] (
				[WebApplicationId],
				[SiteId],
				[WebId] 
			)
			VALUES (
				@WebApplicationId,
				@SiteId,
				@WebId
			)			
	END
    ELSE IF @SiteId IS NOT NULL
    BEGIN
		IF NOT EXISTS(SELECT [Id] FROM [Partitions] WHERE 
			[WebApplicationId] = @WebApplicationId AND [SiteId] = @SiteId)
			INSERT INTO
				[Partitions] (
				[WebApplicationId],
				[SiteId]
			)
			VALUES (
				@WebApplicationId,
				@SiteId
			)			
	END
    ELSE IF @WebApplicationId IS NOT NULL
    BEGIN
		IF NOT EXISTS(SELECT [Id] FROM [Partitions] WHERE 
			[WebApplicationId] = @WebApplicationId)
			INSERT INTO
				[Partitions] (
				[WebApplicationId]
			)
			VALUES (
				@WebApplicationId
			)    
    END
    
    SELECT TOP (1)
		@PartitionId = [Id]
	FROM
		[Partitions]
	WHERE
		[WebApplicationId] = @WebApplicationId 
		AND (@SiteId IS NULL OR [SiteId] = @SiteId) AND
		(@WebId IS NULL OR [WebId] = @WebId) AND 
		(@ListId IS NULL OR [ListId] = @ListId)
END
GO

-- =============================================
-- Author:		Eswar Prakash
-- Create date: 02/2012
-- Description:	Gets a storage item with the specified name and partition information
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetStorageItem]
	-- Add the parameters for the stored procedure here
	@WebApplicationId uniqueidentifier,
	@SiteId uniqueidentifier,
	@WebId uniqueidentifier,
	@ListId uniqueidentifier,
	@Name nvarchar(50)
AS
DECLARE
	@PartitionId int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	EXEC sp_EnsurePartition @WebApplicationId, @SiteId, @WebId, @ListId, @PartitionId = @PartitionId OUTPUT
	
	SELECT TOP (1)
		[Id],
		[Name],
		[TypeFullName],
		[CreatedBy],
		[CreatedDate],
		[ModifiedBy],
		[ModifiedDate],
		[Data]
	FROM
		[Storage]
	WHERE
		[Name] = @Name AND
		[PartitionId] = @PartitionId
END
GO

-- =============================================
-- Author:		Eswar Prakash
-- Create date: 02/2012
-- Description:	Adds a storage item with the specified name and partition information
-- =============================================
CREATE PROCEDURE [dbo].[sp_AddStorageItem]
	-- Add the parameters for the stored procedure here
	@WebApplicationId uniqueidentifier,
	@SiteId uniqueidentifier,
	@WebId uniqueidentifier,
	@ListId uniqueidentifier,
	@Name nvarchar(50),
	@TypeFullName nvarchar(256),
	@CreatedBy nvarchar(256),
	@Data varbinary(MAX)
AS
DECLARE
	@PartitionId int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	EXEC sp_EnsurePartition @WebApplicationId, @SiteId, @WebId, @ListId, @PartitionId = @PartitionId OUTPUT
	
	INSERT INTO
		[Storage] (
			[PartitionId],
			[Name],
			[TypeFullName],
			[CreatedBy],
			[CreatedDate],
			[ModifiedBy],
			[ModifiedDate],
			[Data] )
	VALUES (
		@PartitionId,
		@Name,
		@TypeFullName,
		@CreatedBy,
		GETDATE(),
		@CreatedBy,
		GETDATE(),
		@Data
		)
	
	SELECT 
		[Id],
		[Name],
		[TypeFullName],
		[CreatedBy],
		[CreatedDate],
		[ModifiedBy],
		[ModifiedDate],
		[Data]
	FROM
		[Storage]
	WHERE
		[Name] = @Name AND
		[TypeFullName] = @TypeFullName AND
		[PartitionId] = @PartitionId
END
GO

-- =============================================
-- Author:		Eswar Prakash
-- Create date: 02/02
-- Description:	Deletes the storage item from the storage database
-- =============================================
CREATE PROCEDURE [dbo].[sp_DeleteStorageItem]
	-- Add the parameters for the stored procedure here
	@WebApplicationId uniqueidentifier,
	@SiteId uniqueidentifier,
	@WebId uniqueidentifier,
	@ListId uniqueidentifier,
	@Name nvarchar(50)
AS
DECLARE
	@PartitionId int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	EXEC sp_EnsurePartition @WebApplicationId, @SiteId, @WebId, @ListId, @PartitionId = @PartitionId OUTPUT
	
	IF @PartitionId IS NOT NULL
		DELETE FROM
			[Storage]
		WHERE
			[PartitionId] = @PartitionId AND
			[Name] = @Name							
END
GO

-- =============================================
-- Author:		Eswar Prakash
-- Create date: 02/2012
-- Description:	Updates a storage item with the specified name and partition information
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateStorageItem]
	-- Add the parameters for the stored procedure here
	@WebApplicationId uniqueidentifier,
	@SiteId uniqueidentifier,
	@WebId uniqueidentifier,
	@ListId uniqueidentifier,
	@Name nvarchar(50),
	@TypeFullName nvarchar(256),
	@ModifiedBy nvarchar(256),
	@Data varbinary(MAX)
AS
DECLARE
	@PartitionId int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	EXEC sp_EnsurePartition @WebApplicationId, @SiteId, @WebId, @ListId, @PartitionId = @PartitionId OUTPUT
		
	UPDATE 
		[Storage] 
	SET
		[Data]  = @Data,
		[ModifiedBy] = @ModifiedBy,
		[ModifiedDate] = GETDATE()
	WHERE
		[Name] = @Name AND
		[PartitionId] = @PartitionId
		AND [TypeFullName] = @TypeFullName
	
	SELECT 
		[Id],
		[Name],
		[TypeFullName],
		[CreatedBy],
		[CreatedDate],
		[ModifiedBy],
		[ModifiedDate],
		[Data]
	FROM
		[Storage]
	WHERE
		[Name] = @Name AND
		[TypeFullName] = @TypeFullName AND
		[PartitionId] = @PartitionId
END
GO


-- Provision roles and grant rights
CREATE ROLE [WSS_WPG]
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Storage] TO [WSS_WPG]
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[Partitions] TO [WSS_WPG]
GO
GRANT EXECUTE ON [dbo].[sp_AddStorageItem] TO [WSS_WPG]
GO
GRANT EXECUTE ON [dbo].[sp_EnsurePartition] TO [WSS_WPG]
GO
GRANT EXECUTE ON [dbo].[sp_GetStorageItem] TO [WSS_WPG]
GO
GRANT EXECUTE ON [dbo].[sp_DeleteStorageItem] TO [WSS_WPG]
GO
GRANT EXECUTE ON [dbo].[sp_UpdateStorageItem] TO [WSS_WPG]
GO
